# @FileName  : msql.py
# @Time      : 2021/10/6 0014  9:15
# @Author    : LuZhaoHui
# @Software  : PyCharm

from pymysql import *
from conf.conf import *
from tool.file import *


class mysqlProc(QObject):
    update_item = pyqtSignal(bool)

    def __init__(self, host, port, user, password, db, connect_timeout=3, read_timedelay=5):
        super().__init__()

        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db = db
        self.conn_timeout = connect_timeout
        self.read_timeput = read_timedelay
        self.isShiju = 'shiju' == self.db[:5]
        #
        self.conn = None
        self.cur = None
        self.err = None
        self.ret = 0
        self.status = False
        save_log("mysql thread start")

    def __del__(self):
        save_log("mysql thread end")

    def exit(self):
        self.close()
        self.__del__()

    def close(self):
        if self.cur != None:
            self.cur.close()
            self.cur = None
        if self.conn != None:
            self.conn.close()
            self.conn = None
        self.status = False
        self.update_item.emit(self.status)

    def open(self):
        self.err = None
        try:
            if self.conn == None:
                self.err = ''
                if len(self.host) > 0 and self.port > 0 and len(self.user) > 0:
                    self.conn = connect(host=self.host,
                                        port=self.port,
                                        user=self.user,
                                        passwd=self.password,
                                        db=self.db,
                                        connect_timeout=self.conn_timeout,
                                        read_timeout=self.read_timeput)
            if self.conn != None and self.cur == None:
                self.cur = self.conn.cursor()
        except Exception as e:
            self.err = e
            self.conn = None
            self.close()
            save_log(curFunc(e))
        self.status = self.conn != None
        self.update_item.emit(self.status)
        return self.status

    def getExec(self, sqls, all=False):
        self.err = None
        self.ret = 0
        save_log(sqls)
        try:
            if self.open():
                self.ret = self.cur.execute(sqls)
                self.conn.commit()
        except Exception as e:
            self.close()
            self.err = e
            save_log(curFunc(e))
        if self.ret > 0:
            if all:
                data = self.cur.fetchall()
            else:
                data = self.cur.fetchone()
            return data
        if all:
            return ()
        return 0

    def dbType(self, fType):
        if not self.isShiju or fType == None:
            return '个人'
        else:
            if fType == '':
                return '空'
            else:
                return SHIJUNAME[fType]

    # 搜索文件
    def findResult(self, imgFile):
        sqls = ("call findResult(\'%s\') " % imgFile)
        return self.getExec(sqls)

    # 读取结果-readResult
    def readResult(self, imgFile):
        sqls = ("call readResult(\'%s\') " % imgFile)
        return self.getExec(sqls)

    def getShiju(self, pId, fileType):
        sqls = ("call get%s(%d) " % (SHIJUTYPE[fileType], pId))
        # print(sqls)
        return self.getExec(sqls)

    def getSpclUser(self, sId):
        sqls = ("call getSpclUser(%d) " % sId)
        data = self.getExec(sqls, all=True)
        plist = []
        for p in data:
            plist.append(p)
        return plist

    def getXczUser(self, sId):
        sqls = ("call getXczUser(%d) " % sId)
        data = self.getExec(sqls, all=True)
        plist = []
        for p in data:
            plist.append(p)
        return plist

    def getQstkUser(self, sId):
        sqls = ("call getQstkUser(%d) " % sId)
        data = self.getExec(sqls, all=True)
        plist = []
        for p in data:
            plist.append(p)
        return plist

    def getWhkUser(self, sId):
        sqls = ("call getWhkUser(%d) " % sId)
        data = self.getExec(sqls, all=True)
        plist = []
        for p in data:
            plist.append(p)
        return plist

    def getZqcgUser(self, zId):
        sqls = ("call getZqcgUser(%d) " % zId)
        data = self.getExec(sqls, all=True)
        plist = []
        for p in data:
            plist.append(p)
        return plist

    def getZhclUser(self, zId):
        sqls = ("call getZhclUser(%d) " % zId)
        data = self.getExec(sqls, all=True)
        plist = []
        for p in data:
            plist.append(p)
        return plist

    def getPerson(self, pId):
        sqls = ("call getPerson(%d) " % pId)
        return self.getExec(sqls)

    def getPerson1(self, pId):
        sqls = ("call getPerson1(%d) " % pId)
        return self.getExec(sqls)

    def getCailiao(self, pclId):
        sqls = ("call getCailiao(%d) " % pclId)
        return self.getExec(sqls)

    def readShiju(self, fileId, fileType):
        sqls = ("call read%s(%d) " % (SHIJUTYPE[fileType], fileId))
        return self.getExec(sqls)

    # 读取结果-readPerson
    def readPerson(self, fileId):
        sqls = ("call readPerson(%d) " % fileId)
        data = self.getExec(sqls, all=True)
        plist = []
        for p in data:
            plist.append(p[0])
        return plist

    def readPerson0(self, fileId):
        sqls = ("call readPerson0(%d) " % fileId)
        data = self.getExec(sqls, all=True)
        plist = []
        for p in data:
            plist.append(p[0])
        return plist

    # 读取结果-readCailiao
    def readCailiao(self, fileId):
        sqls = ("call readCailiao(%d) " % fileId)
        data = self.getExec(sqls, all=True)
        if len(data) == 0:
            return 0
        if len(data[0]) == 0:
            return 0
        return data[0][0]

    # 建立图片结果记录-addResult
    def addResult(self, imgFile, model, type, text, alltext, result, proc_time):
        sqls = ("call addResult(\'%s\',\'%s\',\'%s\',\'%s\',\"%s\",\"%s\",%d) "
                %
                (imgFile, model, type, text,
                 alltext.replace('\n', ' ').replace('\\n', '').replace('\'', '’').replace('\"', '“'),
                 result.replace('\\n', '').replace('\'', '’').replace('\"', '“'),
                 proc_time)
                )
        return self.getExec(sqls)

    def addResultExt(self, imgFile, model, type, text, title, head, attrs, alltext, result, proc_time):
        sqls = ("call addResultExt(\'%s\',\'%s\',\'%s\',\'%s\',\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",%d) "
                %
                (imgFile, model, type, text,
                 title.replace('\n', ' ').replace('\\n', '').replace('\'', '’').replace('\"', '“'),
                 head.replace('\n', ' ').replace('\\n', '').replace('\'', '’').replace('\"', '“'),
                 attrs,
                 alltext.replace('\n', ' ').replace('\\n', '').replace('\'', '’').replace('\"', '“'),
                 result.replace('\\n', '').replace('\'', '’').replace('\"', '“'),
                 proc_time)
                )
        return self.getExec(sqls)

    # 建立个人记录-addPerson
    def addPerson(self, fileId, name, alias, gender, birthday, idcard,
                  ethnic, hname, htype, place, qualification, workUnit, address, currentAddress,
                  marry, moveMarket, moveLocal, moveOut, registerDate):
        sqls = (("call addPerson(%d,\"%s\",\"%s\",%d,\"%s\",\"%s\","
                 "\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\","
                 "%d,\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, name, alias, gender, birthday, idcard,
                 ethnic, hname, htype, place, qualification, workUnit, address, currentAddress,
                 marry, moveMarket, moveLocal, moveOut, registerDate)
                )
        return self.getExec(sqls)

    def updatePerson(self, uId, name, gender, birthday, idcard, currentAddress):
        sqls = (("call updatePerson(%d,\'%s\',%d,\'%s\',\'%s\',\'%s\') ")
                %
                (uId, name, gender, birthday, idcard, currentAddress)
                )
        return self.getExec(sqls)

    def updatePerson1(self, uId, currentAddress):
        sqls = (("call updatePerson1(%d,\'%s\') ")
                %
                (uId, currentAddress)
                )
        return self.getExec(sqls)

    def deletePerson(self, uId):
        sqls = (("call deletePerson(%d) ") % uId)
        return self.getExec(sqls)

    # 建立材料记录-addCailiao
    def addCailiao(self, fileId, title, name, birthday, idcard, gender, jianNum, date, content):
        sqls = (("call addCailiao(%d,\"%s\",\"%s\",\"%s\",\"%s\",%d,\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, title, name, birthday, idcard, gender, jianNum, date, content)
                )
        return self.getExec(sqls)

    def deleteRhcg(self, fId):
        sqls = (("call deleteRhcg(%d) ") % fId)
        return self.getExec(sqls)

    def deleteZqcg(self, fId):
        sqls = (("call deleteZqcg(%d) ") % fId)
        return self.getExec(sqls)

    def deleteZhcl(self, fId):
        sqls = (("call deleteZhcl(%d) ") % fId)
        return self.getExec(sqls)

    def deleteXczSH(self, fId):
        sqls = (("call deleteXczSH(%d) ") % fId)
        return self.getExec(sqls)

    def deleteXczSP(self, fId):
        sqls = (("call deleteXczSP(%d) ") % fId)
        return self.getExec(sqls)

    def deleteXczSQ(self, fId):
        sqls = (("call deleteXczSQ(%d) ") % fId)
        return self.getExec(sqls)

    def deleteSpcl(self, fId):
        sqls = (("call deleteSpcl(%d) ") % fId)
        return self.getExec(sqls)

    def deleteSpclSH(self, fId):
        sqls = (("call deleteSpclSH(%d) ") % fId)
        return self.getExec(sqls)

    def deleteSpclSP(self, fId):
        sqls = (("call deleteSpclSP(%d) ") % fId)
        return self.getExec(sqls)

    def deleteSpclSQ(self, fId):
        sqls = (("call deleteSpclSQ(%d) ") % fId)
        return self.getExec(sqls)

    def deleteQstkSP(self, fId):
        sqls = (("call deleteQstkSP(%d) ") % fId)
        return self.getExec(sqls)

    def deleteWhkSP(self, fId):
        sqls = (("call deleteWhkSP(%d) ") % fId)
        return self.getExec(sqls)

    def deleteQstkSQ(self, fId):
        sqls = (("call deleteQstkSQ(%d) ") % fId)
        return self.getExec(sqls)

    def deleteWhkSQ(self, fId):
        sqls = (("call deleteWhkSQ(%d) ") % fId)
        return self.getExec(sqls)

    def addRhcg(self, fileId, name, gender, birthday, idcard,
                approvalNum, precinct, rhNums, rhAddress,
                lwUnit, swNo, receiver, operator, signDate):
        sqls = (("call addRhcg(%d,\"%s\",%d,\"%s\",\"%s\","
                 "\"%s\",\"%s\",\"%s\",\"%s\","
                 "\"%s\",\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, name, gender, birthday, idcard,
                 approvalNum, precinct, rhNums, rhAddress,
                 lwUnit, swNo, receiver, operator, signDate)
                )
        return self.getExec(sqls)

    def addZqcg(self, fileId, name, idcard,
                approvalNum, address, job, djWork,
                toWork, toAddress, toReasons, signDate,
                approvalWorks, handler, remarks):
        sqls = (("call addZqcg(%d,\"%s\",\"%s\","
                 "\"%s\",\"%s\",\"%s\",\"%s\","
                 "\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, name, idcard,
                 approvalNum, address, job, djWork,
                 toWork, toAddress, toReasons, signDate,
                 approvalWorks, handler, remarks)
                )
        return self.getExec(sqls)

    def addZqcgUser(self, fileId, zqcgId,
                    name, htype, gender, age, birthday, idcard, job):
        sqls = (("call addZqcgUser(%d,%d,\"%s\",\"%s\",%d,%d,\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, zqcgId, name, htype, gender, age, birthday, idcard, job)
                )
        return self.getExec(sqls)

    def addZhcl(self, fileId, title, approvalNum,
                name, nums, gender, birthday, idcard, djWork,
                work, address, reason, other):
        sqls = (("call addZhcl(%d,\"%s\",\"%s\",\"%s\",\"%s\",%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, title, approvalNum, name, nums, gender, birthday, idcard, djWork, work, address, reason, other)
                )
        return self.getExec(sqls)

    def addZhclUser(self, fileId, zId,
                    name, gender, age, birthday, idcard, htype, job):
        sqls = (("call addZhclUser(%d,%d,\"%s\",%d,%d,\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, zId, name, gender, age, birthday, idcard, htype, job)
                )
        return self.getExec(sqls)

    def addXczSH(self, fileId, name, gender, age,
                 address, pOpinion, pToukaoType, pHukouType):
        sqls = (("call addXczSH(%d,\"%s\",%d,%d,\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, name, gender, age,
                 address, pOpinion, pToukaoType, pHukouType)
                )
        return self.getExec(sqls)

    def addXczSP(self, fileId, name, gender, birthday, idcard, pToAddress, pApprovalNum,
                 pPcs, pSnum, pFamilyType, pToReasons, pOpinion):
        sqls = (("call addXczSP(%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, name, gender, birthday, idcard, pToAddress, pApprovalNum,
                 pPcs, pSnum, pFamilyType, pToReasons, pOpinion)
                )
        return self.getExec(sqls)

    def addXczSQ(self, fileId, name, gender, birthday, idcard,
                 address, toAddress, precinct, familyType, toReasons, approvalNum,
                 pPlace, pEdu, pToBjDate, pEthnicity, pMarDate, pReasonText):
        sqls = (("call addXczSQ(%d,\"%s\",%d,\"%s\",\"%s\","
                 "\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\","
                 "\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, name, gender, birthday, idcard,
                 address, toAddress, precinct, familyType, toReasons, approvalNum,
                 pPlace, pEdu, pToBjDate, pEthnicity, pMarDate, pReasonText)
                )
        return self.getExec(sqls)

    def addXczUser(self, fileId, sId,
                   name, gender, age, birthday, idcard, job, htype, approvalNum):
        sqls = (("call addXczUser(%d,%d,\"%s\",%d,%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, sId, name, gender, age, birthday, idcard, job, htype, approvalNum)
                )
        return self.getExec(sqls)

    def addSpcl(self, fileId, title, approvalNum,
                name, idcard, other):
        sqls = (("call addSpcl(%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, title, approvalNum, name, idcard, other)
                )
        return self.getExec(sqls)

    def addSpclSH(self, fileId, name, gender, age,
                  address, pOpinion, pToukaoType, pHukouType):
        sqls = (("call addSpclSH(%d,\"%s\",%d,%d,\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, name, gender, age,
                 address, pOpinion, pToukaoType, pHukouType)
                )
        return self.getExec(sqls)

    def addSpclSP(self, fileId, name, pToAddress, pApprovalNum,
                  pPcs, pSnum, pFamilyType, pToReasons, pOpinion):
        sqls = (("call addSpclSP(%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, name, pToAddress, pApprovalNum,
                 pPcs, pSnum, pFamilyType, pToReasons, pOpinion)
                )
        return self.getExec(sqls)

    def addSpclSQ(self, fileId, name, gender, birthday, idcard,
                  address, toAddress, precinct, familyType, toReasons, approvalNum,
                  pPlace, pEdu, pToBjDate, pEthnicity, pMarDate, pReasonText):
        sqls = (("call addSpclSQ(%d,\"%s\",%d,\"%s\",\"%s\","
                 "\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\","
                 "\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, name, gender, birthday, idcard,
                 address, toAddress, precinct, familyType, toReasons, approvalNum,
                 pPlace, pEdu, pToBjDate, pEthnicity, pMarDate, pReasonText)
                )
        return self.getExec(sqls)

    def addSpclUser(self, fileId, sId,
                    name, gender, age, birthday, idcard, job, htype, approvalNum):
        sqls = (("call addSpclUser(%d,%d,\"%s\",%d,%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, sId, name, gender, age, birthday, idcard, job, htype, approvalNum)
                )
        return self.getExec(sqls)

    def addQstkSP(self, fileId, name, toAddress, approvalNum,
                  pcs, familyType, toReasons, toTime, otherText):
        sqls = (("call addQstkSP(%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, name, toAddress, approvalNum, pcs,
                 familyType, toReasons, toTime, otherText)
                )
        return self.getExec(sqls)

    def addQstkSQ(self, fileId, name, gender, birthday, idcard,
                  address, toAddress, precinct, familyType, approvalNum,
                  toBjDate, ethnicity, marDate, reasonText):
        sqls = (("call addQstkSQ(%d,\"%s\",%d,\"%s\",\"%s\","
                 "\"%s\",\"%s\",\"%s\",\"%s\",\"%s\","
                 "\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, name, gender, birthday, idcard,
                 address, toAddress, precinct, familyType, approvalNum,
                 toBjDate, ethnicity, marDate, reasonText)
                )
        return self.getExec(sqls)

    def addQstkUser(self, fileId, sId,
                    name, gender, age, birthday, idcard, job, htype, approvalNum):
        sqls = (("call addQstkUser(%d,%d,\"%s\",%d,%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, sId, name, gender, age, birthday, idcard, job, htype, approvalNum)
                )
        return self.getExec(sqls)

    def addWhkSP(self, fileId, name, toAddress, approvalNum,
                 pcs, familyType, toReasons, toTime, otherText):
        sqls = (("call addWhkSP(%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, name, toAddress, approvalNum, pcs,
                 familyType, toReasons, toTime, otherText)
                )
        return self.getExec(sqls)

    def addWhkSQ(self, fileId, name, gender, birthday, idcard,
                 address, toAddress, precinct, familyType, approvalNum,
                 toBjDate, ethnicity, marDate, reasonText):
        sqls = (("call addWhkSQ(%d,\"%s\",%d,\"%s\",\"%s\","
                 "\"%s\",\"%s\",\"%s\",\"%s\",\"%s\","
                 "\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, name, gender, birthday, idcard,
                 address, toAddress, precinct, familyType, approvalNum,
                 toBjDate, ethnicity, marDate, reasonText)
                )
        return self.getExec(sqls)

    def addWhkUser(self, fileId, sId,
                   name, gender, age, birthday, idcard, job, htype, approvalNum):
        sqls = (("call addWhkUser(%d,%d,\"%s\",%d,%d,\"%s\",\"%s\",\"%s\",\"%s\",\"%s\") ")
                %
                (fileId, sId, name, gender, age, birthday, idcard, job, htype, approvalNum)
                )
        return self.getExec(sqls)
